OpenRecordset Method Example

This example uses the OpenRecordset method to open five different Recordset objects and display their contents. The OpenRecordsetOutput procedure is required for this procedure to run.

Sub OpenRecordsetX()

   Dim wrkJet As Workspace
   Dim wrkODBC As Workspace
   Dim dbsNorthwind As Database
   Dim conPubs As Connection
   Dim rstTemp As Recordset
   Dim rstTemp2 As Recordset

   ' Open Microsoft Jet and ODBCDirect workspaces, Microsoft 
   ' Jet database, and ODBCDirect connection.
   Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
   Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
   Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")
   
   ' Note: The DSN referenced below must be set to 
   '       use Microsoft Windows NT Authentication Mode to 
   '       authorize user access to the Microsoft SQL Server.
   Set conPubs = wrkODBC.OpenConnection("", , , _
      "ODBC;DATABASE=pubs;DSN=Publishers")

   ' Open five different Recordset objects and display the 
   ' contents of each.

   Debug.Print "Opening forward-only-type recordset " & _
      "where the source is a QueryDef object..."
   Set rstTemp = dbsNorthwind.OpenRecordset( _
      "Ten Most Expensive Products", dbOpenForwardOnly)
   OpenRecordsetOutput rstTemp

   Debug.Print "Opening read-only dynaset-type " & _
      "recordset where the source is an SQL statement..."
   Set rstTemp = dbsNorthwind.OpenRecordset( _
      "SELECT * FROM Employees", dbOpenDynaset, dbReadOnly)
   OpenRecordsetOutput rstTemp

   ' Use the Filter property to retrieve only certain 
   ' records with the next OpenRecordset call.
   Debug.Print "Opening recordset from existing " & _
      "Recordset object to filter records..."
   rstTemp.Filter = "LastName >= 'M'"
   Set rstTemp2 = rstTemp.OpenRecordset()
   OpenRecordsetOutput rstTemp2

   Debug.Print "Opening dynamic-type recordset from " & _
      "an ODBC connection..."
   Set rstTemp = conPubs.OpenRecordset( _
      "SELECT * FROM stores", dbOpenDynamic)
   OpenRecordsetOutput rstTemp

   ' Use the StillExecuting property to determine when the 
   ' Recordset is ready for manipulation.
   Debug.Print "Opening snapshot-type recordset based " & _
      "on asynchronous query to ODBC connection..."
   Set rstTemp = conPubs.OpenRecordset("publishers", _
      dbOpenSnapshot, dbRunAsync)
   Do While rstTemp.StillExecuting
      Debug.Print "  [still executing...]"
   Loop
   OpenRecordsetOutput rstTemp

   rstTemp.Close
   dbsNorthwind.Close
   conPubs.Close
   wrkJet.Close
   wrkODBC.Close

End Sub

Sub OpenRecordsetOutput(rstOutput As Recordset)

   ' Enumerate the specified Recordset object.
   With rstOutput
      Do While Not .EOF
         Debug.Print , .Fields(0), .Fields(1)
         .MoveNext
      Loop
   End With

End Sub